package com.opennews.openplatform

import com.opennews.openplatform.constants.IncomeExpenseTypeConstant
import com.opennews.openplatform.constants.PaymentTypeConstant
import com.opennews.openplatform.dto.IncomeExpenseDetailQueryArgument
import grails.compiler.GrailsCompileStatic
import grails.gorm.transactions.Transactional
import groovy.transform.CompileDynamic
import org.hibernate.criterion.CriteriaSpecification
import org.hibernate.sql.JoinType

@Transactional(readOnly = true)
@GrailsCompileStatic
class JourneyIncomeExpenseDetailService {
    BankService bankService
    UserService userService

    /**
     * Queries all journey income/expense which associated with provided account group.
     * @return The list of map instance which contains keys below.
     *          id: String of the income expense detail id.
     *          location: String of the income or expense location.
     *          currency: String of the income or expense currency.
     *          amount: Float of the income expense detail amount.
     *          type: String of the type. It's income or expense.
     *          paymentType: String of the payment type which how the expense payed.
     *          bankId: String of the band id of the bank if the expense is payed by card.
     *          bankShortName: String of the short name of the bank if the expense is payed by card.
     *          bankLogoUrl: String of the logo url of the bank if the expense is payed by card.
     *          bankCardId: String of the id of the bank card if the expense is payed by card.
     *          bankCardName: String of the name of the bank card if the expense is payed by card.
     *          bankCardIsCreditCard: Bool of if this bank card is credit card.
     *          details: String of the income or expense detail.
     */
    @CompileDynamic
    List<Map<String, ?>> queryListByJourney(String journeyId, IncomeExpenseDetailQueryArgument queryArgument) {
        // Queries detail by account group id.
        JourneyIncomeExpenseDetail.createCriteria().list {
            // Transforms the result data format to Map instance.
            resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)

            if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
                // Sets the join type for journey property in order to get its region.
                createAlias("journey", "journey", JoinType.INNER_JOIN)

                // Sets the join type for region property in order to get its title.
                createAlias("journey.region", "region", JoinType.INNER_JOIN)
            }

            // Sets the join type for bank card property in order to get its id and name.
            createAlias("bankCard", "bankCard", JoinType.LEFT_OUTER_JOIN)

            // Sets the join type for bank property in order to get its short name.
            createAlias("bankCard.bank", "bank", JoinType.LEFT_OUTER_JOIN)

            // Sets the query condition.
            and {
                eq("accountGroup.id", queryArgument.accountGroupId)

                if (!SharedUtil.isNullOrEmpty(journeyId)) {
                    eq("journey.id", journeyId)
                }

                if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
                    // When searching with AND condition for multiple keywords then we just need to wrap each keyword into one OR section.
                    // The keyword supports separated by blank string.
                    // When it occurs we are splitting the keyword with blank string and gets multiple keywords.
                    // The multiple keywords will be used to search within OR condition.
                    // Regarding the dynamic property in criteria please check on https://stackoverflow.com/questions/11550507/build-createcriteria-in-grails-dynamically-and-in-a-dry-way
                    queryArgument.keyword.split("\\s+").each { String key ->
                        // Equivalent to SQL like expression.
                        or {
                            like("details", "%$key%")
                            like("remark", "%$key%")
                        }
                    }
                }
            }

            // Specifies the property we need.
            projections {
                if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
                    property("journey.id", "journeyId")
                    property("journey.startDate", "journeyStartDate")
                    property("journey.endDate", "journeyEndDate")
                    property("region.title", "regionTitle")
                }

                property("id", "id")
                property("incomeExpenseDate", "incomeExpenseDate")
                property("location", "location")
                property("currency", "currency")
                property("amount", "amount")
                property("type", "type")
                property("paymentType", "paymentType")
                property("bank.id", "bankId")
                property("bank.shortName", "bankShortName")
                property("bankCard.id", "bankCardId")
                property("bankCard.name", "bankCardName")
                property("bankCard.isCreditCard", "bankCardIsCreditCard")
                property("details", "details")
            }

            // Sets the orderby.
            order("incomeExpenseDate", "desc")
            order("type", "desc")
            order("bankCard", "asc")
            order("lastUpdated", "desc")

            if (queryArgument.pageSize != null) {
                // How many rows are return for this trip.
                maxResults(queryArgument.pageSize)
            }

            if (queryArgument.pageSize != null && queryArgument.pageIndex != null) {
                // Where to take the rows.
                firstResult(queryArgument.pageSize * queryArgument.pageIndex)
            }
        }.each { Map<String, ?> item ->
            // Adds bankLogoUrl key to the map item.
            item.bankLogoUrl = bankService.getLogoUrl(item.bankShortName.toString())
        } as List<Map<String, ?>>
    }

    /**
     * Queries journey income expense details by its id.
     * @param accountGroupId: The string of user account group id.
     * @param id: The string of the income expense detail id.
     * @return The map instance which contains keys below.
     *          id: String of the income expense detail id.
     *          journeyId: String of the journey id.
     *          location: String of the income or expense location.
     *          currency: String of the income or expense currency.
     *          amount: Float of the income expense detail amount.
     *          type: String of the type. It's income or expense.
     *          paymentType: String of the payment type which how the expense payed.
     *          bankId: String of the band id of the bank card if the expense is payed by card.
     *          bankShortName: String of the short name of the bank card if the expense is payed by card.
     *          bankCardId: String of the id of the bank card if the expense is payed by card.
     *          bankCardName: String of the name of the bank card if the expense is payed by card.
     *          details: String of the income or expense detail.
     *          remark: String of the necessary extra remark.
     *          createdBy: String of username of who created this item.
     *          updatedBy: String of username of who updated this item.
     *          dateCreated: Date of when this detail created.
     *          lastUpdated: Date of when this detail updated.
     */
    @CompileDynamic
    Map<String, ?> queryDetails(String accountGroupId, String id) {
        // Queries detail by account group id.
        Map<String, ?> result = JourneyIncomeExpenseDetail.createCriteria().get {
            // Transforms the result data format to Map instance.
            resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)

            // Sets the join type for bank card property in order to get its id and name.
            createAlias("bankCard", "bankCard", JoinType.LEFT_OUTER_JOIN)

            // Sets the join type for bank property in order to get its short name.
            createAlias("bankCard.bank", "bank", JoinType.LEFT_OUTER_JOIN)

            // Sets the query condition.
            and {
                eq("accountGroup.id", accountGroupId)
                eq("id", id)
            }

            // Specifies the property we need.
            projections {
                property("id", "id")
                property("journey.id", "journeyId")
                property("incomeExpenseDate", "incomeExpenseDate")
                property("location", "location")
                property("currency", "currency")
                property("amount", "amount")
                property("type", "type")
                property("paymentType", "paymentType")
                property("bank.id", "bankId")
                property("bank.shortName", "bankShortName")
                property("bankCard.id", "bankCardId")
                property("bankCard.name", "bankCardName")
                property("details", "details")
                property("remark", "remark")
                property("latitude", "latitude")
                property("longitude", "longitude")
                property("address", "address")
                property("formattedAddress", "formattedAddress")
                property("province", "province")
                property("city", "city")
                property("district", "district")
                property("createdBy", "createdBy")
                property("updatedBy", "updatedBy")
                property("dateCreated", "dateCreated")
                property("lastUpdated", "lastUpdated")
            }
        } as Map<String, ?>

        if (result == null) {
            return [:]
        }

        // Queries createdBy & updatedBy basic info.
        def userDetails = userService.queryBasicInfo(accountGroupId, [result.createdBy, result.updatedBy])
        result.createdBy = userDetails.find { it.username == result.createdBy }
        result.updatedBy = userDetails.find { it.username == result.updatedBy }

        // Sets the bank logo url based on its short name.
        result.put("bankLogoUrl", bankService.getLogoUrl(result.bankShortName.toString()))

        return result
    }

    /**
     * Queries all distinct income/expense years.
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integer of existing year.
     */
    List<Integer> queryExistingDistinctYears(String accountGroupId) {
        def hql = """
                    SELECT DISTINCT
                        YEAR(incomeExpenseDate)
                    FROM
                        JourneyIncomeExpenseDetail
                    WHERE
                        accountGroup.id = :accountGroupId
                    ORDER BY
                        YEAR(incomeExpenseDate) DESC
                  """

        return JourneyIncomeExpenseDetail.executeQuery(hql, [accountGroupId: accountGroupId]).sort().reverse()
    }

    @CompileDynamic
    List<Map<String, ?>> queryCoordinates(String accountGroupId, String year) {
        // Locations contain same formatted address but different coordinates which we like to take only one match.
        // https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
        def hql = """
                            SELECT
                                formattedAddress,
                                latitude,
                                longitude
                            FROM
                                JourneyIncomeExpenseDetail
                            WHERE
                                id IN (
                                    SELECT
                                        MAX(id)
                                    FROM
                                        JourneyIncomeExpenseDetail
                                    WHERE
                                        accountGroup.id = :accountGroupId AND
                                        type = :type AND
                                        latitude IS NOT NULL AND
                                        longitude IS NOT NULL AND
                                        formattedAddress IS NOT NULL AND
                                        formattedAddress <> '' AND
                                        (
                                            :year IS NULL OR 
                                            :year = '' OR 
                                            YEAR(incomeExpenseDate) = :year
                                        )
                                    GROUP BY
                                        formattedAddress
                                )
                        """

        return JourneyIncomeExpenseDetail.executeQuery(hql, [
            type: IncomeExpenseTypeConstant.EXPENSE,
            accountGroupId: accountGroupId,
            year: year
        ]).collect {[
            formattedAddress: it[0],
            latitude: it[1],
            longitude: it[2]
        ]} as List<Map<String, ?>>
    }

    /**
     * Adds a new record of JourneyIncomeExpenseDetail with posted data.
     * The username used to query the account group id and saved to createdBy and updatedBy.
     * @param requestBodyData: The map instance which contains the keys with same names as JourneyIncomeExpenseDetail properties.
     * @param accountGroupId: The string of user account group id.
     * @param username: The string of username which is saved to createdBy and updatedBy.
     */
    @Transactional()
    void add(Map<String, ?> requestBodyData, String accountGroupId, String username) {
        // Creates new JourneyIncomeExpenseDetail.
        def incomeExpenseDetail = new JourneyIncomeExpenseDetail()

        // Updates its properties with posted data.
        updateProperties(incomeExpenseDetail, requestBodyData, username)

        // Associates with account group.
        incomeExpenseDetail.accountGroup = AccountGroup.proxy(accountGroupId)

        // Associates with journey.
        incomeExpenseDetail.journey = Journey.proxy((requestBodyData.journey as Map<String, ?>).id.toString())

        // Specifies creator.
        incomeExpenseDetail.createdBy = username

        // Saves change.
        incomeExpenseDetail.save()
    }

    /**
     * Updates existing JourneyIncomeExpenseDetail with posted data.
     * @param accountGroupId: The string of user account group id.
     * @param username: The string of username which saved to updatedBy.
     * @param requestBodyData: The map instance which contains the keys with same names as JourneyIncomeExpenseDetail properties.
     */
    @Transactional()
    void update(String accountGroupId, String username, Map<String, ?> requestBodyData) {
        // Gets JourneyIncomeExpenseDetail by user account group and id.
        def incomeExpenseDetail = JourneyIncomeExpenseDetail.findByAccountGroupAndId(AccountGroup.proxy(accountGroupId), requestBodyData.id.toString())

        // Applies parallel authentication.
        if (incomeExpenseDetail != null) {
            // Updates its properties with posted data.
            updateProperties(incomeExpenseDetail, requestBodyData, username)

            // Saves change.
            incomeExpenseDetail.save()
        }
    }

    /**
     * Deletes the JourneyIncomeExpenseDetails with provides list of id.
     * @param accountGroupId: The string of user account group id.
     * @param ids The string list of id.
     */
    @Transactional()
    @CompileDynamic
    void delete(String accountGroupId, List<String> ids) {
        if (ids.size() > 0) {
            // Executes batch deleting by DetachedCriteria.
            JourneyIncomeExpenseDetail.where {
                id in ids &&
                    accountGroup == AccountGroup.proxy(accountGroupId)
            }.deleteAll()
        }
    }

    /**
     * Updates input JourneyIncomeExpenseDetail properties.
     * @param incomeExpenseDetail: The instance of JourneyIncomeExpenseDetail which properties will be updated.
     * @param requestBodyData: The map instance which contains the keys with same names as JourneyIncomeExpenseDetail properties.
     * @param username: The string of username which saved to updatedBy.
     */
    private void updateProperties(JourneyIncomeExpenseDetail incomeExpenseDetail, Map<String, ?> requestBodyData, String username) {
        //// Stores the posted journey data.
        //Journey journey
        //
        //// Gets the bank card proxy if needed.
        //if (requestBodyData.journey != null && (requestBodyData.journey as Map<String, ?>).id != null) {
        //    def journeyData = requestBodyData.journey as Map<String, ?>
        //    journey = Journey.proxy(journeyData.id.toString())
        //
        //    // If we don't remove this key we cannot use the constructor to assign value.
        //    // new JourneyIncomeExpenseDetail(requestBodyData) will cause error since it creates a new journey with it.
        //    // But properties.putAll() can live with it.
        //    // Leaving this line of code just in case.
        //    requestBodyData.remove("journey")
        //}
        //
        //// Updates the journey property.
        //incomeExpenseDetail.journey = journey

        // Stores the posted bank card data.
        BankCard bankCard

        // Gets the bank card proxy if needed.
        if (requestBodyData.bankCard != null && (requestBodyData.bankCard as Map<String, ?>).id != null) {
            def bankCardData = requestBodyData.bankCard as Map<String, ?>
            bankCard = BankCard.proxy(bankCardData.id.toString())

            // If we don't remove this key we cannot use the constructor to assign value.
            // new JourneyIncomeExpenseDetail(requestBodyData) will cause error since it creates a new bank card with it.
            // But properties.putAll() can live with it.
            // Leaving this line of code just in case.
            requestBodyData.remove("bankCard")
        }

        // Updates the bank card property.
        incomeExpenseDetail.bankCard = bankCard

        // Updates all properties which requestBodyData contains.
        incomeExpenseDetail.properties.putAll(requestBodyData)

        // Specifies updater.
        incomeExpenseDetail.updatedBy = username

        // Sets the default payment type if needed.
        if (SharedUtil.isNullOrEmpty(incomeExpenseDetail.paymentType)) {
            incomeExpenseDetail.paymentType = PaymentTypeConstant.CASH
        }

        // Just in case user inputs negative number for income and positive number for expense.
        // We take the absolute value first.
        incomeExpenseDetail.amount = incomeExpenseDetail.amount.abs()

        // If it is expense we take it as negative number.
        if (incomeExpenseDetail.type == IncomeExpenseTypeConstant.EXPENSE) {
            incomeExpenseDetail.amount = -incomeExpenseDetail.amount
        }
    }
}
